<?php

class Jobs extends CI_Model {

    public function __construct() {
        if (!defined("JOB_STATUS_0"))
            define("JOB_STATUS_0", 'Awaiting Quotation');
        if (!defined("JOB_STATUS_1"))
            define("JOB_STATUS_1", 'Price quoted');
        if (!defined("JOB_STATUS_2"))
            define("JOB_STATUS_2", 'Offer accepted (awaiting advance payment)');
        if (!defined("JOB_STATUS_3"))
            define("JOB_STATUS_3", 'Offer accepted (price part paid)');
        if (!defined("JOB_STATUS_4"))
            define("JOB_STATUS_4", 'Awaiting Job Execution (UNPAID)');
        if (!defined("JOB_STATUS_5"))
            define("JOB_STATUS_5", 'Waiting for payment');
        if (!defined("JOB_STATUS_6"))
            define("JOB_STATUS_6", 'Waiting to receive payment');
        if (!defined("JOB_STATUS_7"))
            define("JOB_STATUS_7", 'Job closed');
        if (!defined("JOB_STATUS_8"))
            define("JOB_STATUS_8", 'Cancelled');
        if (!defined("JOB_STATUS_9"))
            define("JOB_STATUS_9", 'Denied Offer');
        if (!defined("JOB_STATUS_10"))
            define("JOB_STATUS_10", 'Expired Offer');
        if (!defined("JOB_STATUS_11"))
            define("JOB_STATUS_11", 'Deleted by admin');
        if (!defined("JOB_STATUS_12"))
            define("JOB_STATUS_12", 'No action taken');
        if (!defined("JOB_STATUS_13"))
            define("JOB_STATUS_13", 'Awaiting Job Execution (PAID)');
        if (!defined("JOB_STATUS_14"))
            define("JOB_STATUS_14", 'Awaiting Job Execution (DEPOSIT PAID)');
        // newphp - define new variables
        if (!defined("JOB_DAYS_OVERDUE"))
            define("JOB_DAYS_OVERDUE", 14);
    }

    function sendMailAlert() {
        $this->load->library('email');
        $this->load->model('Jobs');
        $mailconf['mailtype'] = 'html';
        $this->email = new CI_Email($mailconf);

        $this->email->from('ivy@bookabus.sg', 'Ivy');

        $user = & Users::getInstance();

        $sql = "SELECT * FROM `job_group` WHERE `status`=0 AND `start_date`< NOW()";
        $elapse = $this->db->query($sql);
        $jobs = $elapse->result();
        $count_elapse = count($jobs);
        if ($count_elapse > 0) {
            for ($i = 0; $i < $count_elapse; $i++) {
                $id = $this->Jobs->getID($jobs[$i]);

                $this->email->message('Job #' . $id . ' is expired. It will be cancelled!');

                if ($jobs[$i]->owner_id != 0) {
                    // send email to customer
                    $user_info = $user->getUserInfo($jobs[$i]->owner_id);
                    $this->email->subject('Job #' . $id . ' is expired.');
                    $this->email->to($user_info->email);
                    $this->email->send();
                }

                // send email to admin
                $this->email->subject('Job #' . $id . ' is expired.');
                $this->email->to('daniel_champion1101@yahoo.com');
                $this->email->send();

                // send email to tester
                $this->email->subject('Job #' . $id . ' is expired.');
                $this->email->to('hoang.dinh21@gmail.com');
                $this->email->send();
            }
        }

        $sql = "SELECT * FROM `job_group` WHERE `status`=1 AND `invalid` >0 AND `invalid`< NOW()";
        $elapse = $this->db->query($sql);
        $jobs = $elapse->result();
        $count_elapse = count($jobs);
        if ($count_elapse > 0) {
            for ($i = 0; $i < $count_elapse; $i++) {
                $id = $this->Jobs->getID($jobs[$i]);

                $this->email->message('Job #' . $id . ' is expired. It will be cancelled!');

                if ($jobs[$i]->owner_id != 0) {
                    // send email to customer
                    $user_info = $user->getUserInfo($jobs[$i]->owner_id);
                    $this->email->subject('JobID #' . $id . ' is expired.');
                    $this->email->to($user_info->email);
                    $this->email->send();
                }

                // send email to admin
                $this->email->subject('JobID #' . $id . ' is expired.');
                $this->email->to('daniel_champion1101@yahoo.com');
                $this->email->send();

                // send email to tester
                $this->email->subject('JobID #' . $id . ' is expired.');
                $this->email->to('hoang.dinh21@gmail.com');
                $this->email->send();
            }
        }
    }

    function updateJobStatus() {

        // set cancel for job eslape		
        $sql = "UPDATE `job_group` SET `status`=8 WHERE `status`=0 AND `start_date`< NOW()";
        $this->db->query($sql);

        $sql = "UPDATE `job_group` SET `status`=8 WHERE `status`=1 AND `invalid` >0 AND `invalid`< NOW()";
        $this->db->query($sql);

        $sql = "UPDATE `job_group` SET `status`=13 WHERE `status`=14 AND (`price` < `paid` OR `price` = `paid`)";
        $this->db->query($sql);

        $sql = "UPDATE `job_group` SET `status`=7 WHERE `status`=13 AND `end_date` >0 AND `end_date`< NOW()";
        $this->db->query($sql);
    }

    function updateJobTime() {

        $sql = "SELECT * FROM `job_group` WHERE `gid` > 0";
        $elapse = $this->db->query($sql);
        $trips = $elapse->result();
        $count_elapse = count($trips);
        if ($count_elapse > 0) {
            for ($i = 0; $i < $count_elapse; $i++) {
                $id = $trips[$i];
                $start = $id->invalid;
                $elapsed_date = $this->Diff($start);
                $sql = "UPDATE `job_group` SET `elapsed_date`=" . $elapsed_date . " WHERE `gid`=" . $id->gid; //AND `start_date`< NOW()
                $elapse = $this->db->query($sql);
            }
        }
    }

    function Diff($s2) {

        date_default_timezone_set('Asia/Ho_Chi_Minh');
        $s1 = date("Y-m-d H:i:s");
        $ymd = explode(' ', $s1);
        $ymd1 = explode('-', $ymd[0]);
        $ymd2 = explode(':', $ymd[1]);
        $start = mktime(0, 0, 0, $ymd1[1], $ymd1[2], $ymd1[0]);
        $eymd = explode(' ', $s2);
        $eymd1 = explode('-', $eymd[0]);
        $eymd2 = explode(':', $eymd[1]);
        $end = mktime(0, 0, 0, $eymd1[1], $eymd1[2], $eymd1[0]);
        $result = ($end - $start) / (3600 * 24);
        return $result;
    }

    /**
     * Job order
     * Status:
     * 0 - awaiting quotation
     * 1 - quoted a price
     * 2 - user accepted
     * 3 - user paid
     * 4 - payment accepted
     * 5 - job done, waiting for payment
     * 6 - job done, paid everything
     * 7 - job done, payment accepted, job closed
     * 8 - Cancelled
     * 9 - denied offer
     * 10 - expired offer
     * 
     * @param int $owner
     * @param string $moreinfo
     * @param int $type
     * @param int $draft
     */
    private $_config = array(
        'number_of_overdue_days' => 14
    );

    public function jobOrder($owner, $moreinfo, $type, $draft = 0) {
        if (($type < 0) || ($type > 3)) {
            if ($type == 'term') {
                $type = 2;
            } elseif ($type == 'trip') {
                $type = 0;
            } elseif ($type == 'time') {
                $type = 1;
            } elseif ($type == 'upload') {
                $type = 3;
            }
        }
        $data = array(
            'owner_id' => $owner,
            'sessid' => $this->session->userdata('session_id'),
            'create_date' => date("Y-m-d H:i:s", time()),
            'moreinfo' => $moreinfo,
            'type' => $type,
            'status' => 0,
            'draft' => $draft
        );
        $this->db->insert('job_group', $data);
        $gid = $this->db->insert_id();

        if ($type == 0 || $type == 1) {
            $jobType = 4;
        } else {
            $jobType = $type;
        }

        $jobs = $this->getJobs($owner, $jobType);
        foreach ($jobs as $job) {
            $updateData = array(
                'belongs' => $gid,
            );

            $this->db->where('id', $job->id);
            $this->db->update('jobs', $updateData);
        }

        return $gid;
    }

    public function getPages($total, $perPage) {
        return ceil($total / $perPage);
    }

    /**
     * Get oncoming job orders for user
     * 
     * @param $uid
     */
    public function getOncoming($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();
        // Job orders
        if ($uid < -2) {
            //$sql = "SELECT * FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
            $sql = "SELECT * FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
            //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0";
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
        }
        else {
            if ($uid < 0) {
                //$sql = "SELECT * FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
            else if ($uid > 1000000) {
                $uid = $uid - 1000000;
                //$sql = "SELECT * FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14 ) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
            else {
                //$sql = "SELECT * FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14 ) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          if (!$count) {
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          }
          } */
        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }
        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    public function getOncomingDetails($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            //$sql = "SELECT * FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
            $sql = "SELECT * FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
            //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0";
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
        }
        else {
            if ($uid < 0) {
                //$sql = "SELECT * FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` > 1 AND `status` <= 4 AND `owner_id` > 0 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` <= 6) OR `status`=13 OR `status`=14) AND `owner_id` > 0 AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
            else if ($uid > 1000000) {
                $uid = $uid - 1000000;
                //$sql = "SELECT * FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14 ) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
            else {
                //$sql = "SELECT * FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0 ORDER BY `gid` DESC LIMIT ".$start.", ".$limit;
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14 ) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0) ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = ".$uid." AND `status` < 5 AND `draft` = 0";
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND (`status` <= 6 OR `status`=13 OR `status`=14) AND `draft` = 0 AND (`end_date` > NOW() OR `end_date` =0)";
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          if (!$count) {
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          }
          } */
        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }
        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    public function setMessage($jid, $to, $level) {
        if ($to != 0 && $to != 1)
            $to = 0;

        if ($level == 0) {
            $updateData = array(
                'muser' => $to
            );
        } else {
            $updateData = array(
                'madmin' => $to
            );
        }

        $this->db->where('gid', $jid);
        $this->db->update('job_group', $updateData);
    }

    public function setAdminMoreInfo($jid, $text) {
        $updateData = array(
            'admininfo' => $text
        );

        $this->db->where('gid', $jid);
        $this->db->update('job_group', $updateData);
    }

    /**
     * Get completed job orders for user
     * 
     * @param $uid
     */
    public function getCompleted($uid = -1, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` = 7 AND `owner_id` > 0 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
        } else {
            if ($uid < 0) {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` = 7 AND `owner_id` > 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            } else if ($uid > 1000000) {
                $uid = $uid - 1000000;
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 7 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 7 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          }
         */
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            if ($uid < 0) {
                $user = & Users::getInstance();

                $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
            }

            $return[] = $jarr;
        }

        return $return;
    }

    public function getCompletedDetails($uid = -1, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` = 7 AND `owner_id` > 0 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
        } else {
            if ($uid < 0) {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` = 7 AND `owner_id` > 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            } else if ($uid > 1000000) {
                $uid = $uid - 1000000;
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 7 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 7 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          } */
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            if ($uid < 0) {
                $user = & Users::getInstance();

                $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
            }

            $return[] = $jarr;
        }

        return $return;
    }

    public function getCompletedCount($uid = -1) {
        $return = array();

        // Job orders
        if ($uid < 0) {
            $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 7 AND `owner_id` > 0";
        } else {
            $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 7";
        }
        $query = $this->db->query($sql);
        $jobs = $query->row();

        return $jobs->total;
    }

    /**
     * Get cancelled job orders for user
     * 
     * @param $uid
     */
    public function getCancelled($uid = -1, $start = 0, $limit = 5) {

        // set canceled for job overday		
        $today = time();
        $sql = "UPDATE `job_group` SET `status`=8 WHERE `status`=0 AND `start_date`<'" . $today . "'";
        $query = $this->db->query($sql);

        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` > 7 AND `status` <> 13 AND `status` <> 14 AND `owner_id` > 0 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
        } else {
            if ($uid < 0) {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` > 7 AND `status` <> 13 AND `status` <> 14 AND `owner_id` > 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            } else if ($uid > 1000000) {
                echo $uid;
                $uid = $uid - 1000000;
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` > 7 AND `status` <> 13 AND `status` <> 14 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` > 7 AND `status` <> 13 AND `status` <> 14 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          } */
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            if ($uid < 0) {
                $user = & Users::getInstance();

                $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
            }

            $return[] = $jarr;
        }
        return $return;
    }

    public function getCancelledDetails($uid = -1, $start = 0, $limit = 5) {

        // set canceled for job overday		
        $today = time();
        $sql = "UPDATE `job_group` SET `status`=8 WHERE `status`=0 AND `start_date`<'" . $today . "'";
        $query = $this->db->query($sql);

        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` > 7 AND `status` <> 13 AND `status` <> 14 AND `owner_id` > 0 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
        } else {
            if ($uid < 0) {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `status` > 7 AND `status` <> 13 AND `status` <> 14 AND `owner_id` > 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            } else if ($uid > 1000000) {
                $uid = $uid - 1000000;
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` > 7 AND `status` <> 13 AND `status` <> 14 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` > 7 AND `status` <> 13 AND `status` <> 14 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();
        /*
          foreach ($jobs as $job) {
          $routes = $this->getOrderJobs($job->gid);
          $return[] = array('order' => $job, 'routes' => $routes);
          } */
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            if ($uid < 0) {
                $user = & Users::getInstance();

                $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
            }

            $return[] = $jarr;
        }
        return $return;
    }

    public function getCancelledCount($uid = -1) {
        $return = array();

        // Job orders
        if ($uid < 0) {
            $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` > 7 AND `status` <> 13 AND `status` <> 14 AND `owner_id` > 0";
        } else {
            $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` > 7 AND `status` <> 13 AND `status` <> 14";
        }
        $query = $this->db->query($sql);
        $jobs = $query->row();

        return $jobs->total;
    }

    /**
     * Get drafts for user
     * 
     * @param $uid
     */
    public function getDrafts($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < 0) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `draft` = 1 AND `owner_id` > 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `draft` = 1 AND `owner_id` > 0";
        } else if ($uid > 1000000) {
            $uid = $uid - 1000000;
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1";
        }
        else {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1";
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if ($count) {
            return $jobs[0]->total;
        }

        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $return[] = array('order' => $job, 'routes' => $routes);
        }

        return $return;
    }

    public function getDraftsDetails($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < 0) {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `draft` = 1 AND `owner_id` > 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `draft` = 1 AND `owner_id` > 0";
        } else if ($uid > 1000000) {
            $uid = $uid - 1000000;
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1";
        }
        else {
            $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y') AS `date` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) as `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `draft` = 1";
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if ($count) {
            return $jobs[0]->total;
        }

        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $return[] = array('order' => $job, 'routes' => $routes);
        }

        return $return;
    }

    /**
     * Get jobs that needs quote
     * 
     * @param $uid
     */
    public function getNeedQuote($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT * FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0";
        }
        else {
            if ($uid < 0) {
                $sql = "SELECT * FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0";
            } else {
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 0 AND `draft` = 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }

        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    public function getNeedQuoteDetails($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT * FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0";
        }
        else {
            if ($uid < 0) {
                $sql = "SELECT * FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 0 AND `owner_id` > 0 AND `draft` = 0";
            } else {
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 0 AND `draft` = 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }

        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    /**
     * Get jobs that are waiting for confirmation
     * 
     * @param $uid
     */
    public function getNeedConfirm($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT * FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0";
        }
        else {
            if ($uid < 0) {
                $sql = "SELECT * FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0";
            } else {
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 1 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 1";
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }

        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    public function getNeedConfirmDetails($uid = -1, $count = false, $start = 0, $limit = 5) {
        $return = array();

        // Job orders
        if ($uid < -2) {
            $sql = "SELECT * FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;

            if ($count)
                $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0";
        }
        else {
            if ($uid < 0) {
                $sql = "SELECT * FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` = 1 AND `owner_id` > 0";
            } else {
                $sql = "SELECT * FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 1 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;

                if ($count)
                    $sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `owner_id` = " . $uid . " AND `status` = 1";
            }
        }
        $query = $this->db->query($sql);
        $jobs = $query->result();

        if (!$count) {
            foreach ($jobs as $job) {
                $routes = $this->getOrderJobs($job->gid);
                $jarr = array('order' => $job, 'routes' => $routes);

                if ($uid < 0) {
                    $user = & Users::getInstance();

                    $jarr['userinfo'] = $user->getUserInfo($job->owner_id);
                }

                $return[] = $jarr;
            }
        }

        if ($count) {
            return $jobs[0]->total;
        }

        return $return;
    }

    public function getOrderJob($id) {
        $sql = "SELECT *, DATE_FORMAT(`create_date`, '%e %b %Y, %H:%i') AS `date`, DATEDIFF(CURDATE(), `end_date`) AS `daysOver` FROM `job_group` WHERE `gid` = " . $id;
        $query = $this->db->query($sql);

        return $query->row();
    }

    public function getOrderJobs($id) {
        $sql = "SELECT *, YEAR(`start_date`) AS `y`, MONTH(`start_date`) AS `m`, DAY(`start_date`) AS `d`, HOUR(`start_date`) AS `h`, MINUTE(`start_date`) AS `mi`, YEAR(`end_date`) AS `ey`, MONTH(`end_date`) AS `em`, DAY(`end_date`) AS `ed`, HOUR(`end_date`) AS `eh`, MINUTE(`end_date`) AS `emi` FROM `jobs` WHERE `belongs` = " . $id . " ORDER BY `id` DESC";
        $query = $this->db->query($sql);

        return $query->result();
    }

    public function updateSingle($id, $total_price, $bus_type) {
        $data = array(
            'total_price' => $total_price,
            'bus_type' => $bus_type
        );

        $this->db->where('id', $id);
        $this->db->update('jobs', $data);
    }

    public function updatePrice($id, $price, $tax, $discount, $preq, $terms, $pmethods, $updateStatus = -1, $pterms = "") {
        $data = array(
            'price' => $price,
            'tax' => $tax,
            'discount' => $discount,
            'preq' => $preq,
            'terms' => $terms,
            'pmethods' => $pmethods,
            'pterms' => $pterms
        );

        if ($updateStatus >= 0) {
            $this->changeStatus($id, $updateStatus);
        }

        $this->db->where('gid', $id);
        $this->db->update('job_group', $data);
    }

    public function updateJobPrice($id, $price) {
        $data = array(
            'total_price' => $price
        );

        $this->db->where('id', $id);
        $this->db->update('jobs', $data);
    }

    public function calcStartDate($id) {
        $this->db->where('belongs', $id);
        $this->db->order_by("start_date", "asc");
        $this->db->limit(1);
        $q = $this->db->get('jobs');
        $job = $q->row();

        $sql = "UPDATE `job_group` SET `start_date` = '" . $job->start_date . "' WHERE `gid` = " . (int) $id;
        $this->db->query($sql);
    }

    public function calcEndDate($id) {
        $this->db->where('belongs', $id);
        $this->db->order_by("end_date", "desc");
        $this->db->limit(1);
        $q = $this->db->get('jobs');
        $job = $q->row();

        $sql = "UPDATE `job_group` SET `end_date` = '" . $job->end_date . "' WHERE `gid` = " . (int) $id;
        $this->db->query($sql);
    }

    public function setEndDate($id, $duration, $invalid) {
        $this->db->where('belongs', $id);
        $this->db->order_by("start_date", "desc");
        $this->db->limit(1);
        $q = $this->db->get('jobs');
        $job = $q->row();

        $sql = "UPDATE `job_group` SET `invalid` = '" . $invalid . "', `duration` = " . (int) $duration . ", `start_date` = '" . $job->start_date . "', `end_date` = ADDDATE('" . $job->start_date . "', INTERVAL " . (int) $duration . " DAY) WHERE `gid` = " . (int) $id;
        $this->db->query($sql);
    }

    public function getDuePrice($id = -1) {
        if ($id > 0) {
            //$sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE `status` < 7 AND `owner_id` = ".(int)$id." AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY)";
            //$sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE `status` > 1 AND `status` < 7 AND `owner_id` = " . (int) $id . " AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY)";
            $sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date`";
        } else {
            //$sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE `status` < 7 AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY)";
            $sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date`";
        }
        $q = $this->db->query($sql);
        $res = $q->row();

        if ($res->total > 0) {
            return $res->total;
        }

        return 0;
    }

    public function getDuePriceList($id = 0, $start = 0, $limit = 5) {
        if ($id > 0) {
            if ($id > 1000000) {
                $id = $id - 1000000;
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND `owner_id` = ".(int)$id." AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        } else {
            if ($id < -2) {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;			
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL `pterms` DAY)  AND (`price`-`paid`) > 0 ORDER BY `gid` ASC  LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;			
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL `pterms` DAY)  AND (`price`-`paid`) > 0 ORDER BY `gid` DESC  LIMIT " . $start . ", " . $limit;
            }
        }



        $q = $this->db->query($sql);
        $jobs = $q->result();
        $return = array();
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            $user = & Users::getInstance();

            $jarr['userinfo'] = $user->getUserInfo($job->owner_id);

            $return[] = $jarr;
        }

        return $return;
    }

    public function getDuePriceListDetails($id = 0, $start = 0, $limit = 5) {
        if ($id > 0) {
            if ($id > 1000000) {
                $id = $id - 1000000;
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND `owner_id` = ".(int)$id." AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
            } else {
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        } else {
            if ($id < -2) {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;			
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL `pterms` DAY)  AND (`price`-`paid`) > 0 ORDER BY `start_date` ASC  LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE `status` < 7 AND NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY) LIMIT ".$start.", ".$limit;			
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 LIMIT " . $start . ", " . $limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL `pterms` DAY)  AND (`price`-`paid`) > 0 ORDER BY `start_date` DESC  LIMIT " . $start . ", " . $limit;
            }
        }



        $q = $this->db->query($sql);
        $jobs = $q->result();
        $return = array();
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            $user = & Users::getInstance();

            $jarr['userinfo'] = $user->getUserInfo($job->owner_id);

            $return[] = $jarr;
        }

        return $return;
    }

    public function getOverduePrice($id = -1) {
        if ($id > 0) {
            $sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY)";
        } else {
            $sql = "SELECT SUM(`price`-`paid`) AS `total` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY)";
        }
        $q = $this->db->query($sql);
        $res = $q->row();

        if ($res->total > 0) {
            return $res->total;
        }

        return 0;
    }

    public function getOverduePriceList($id = 0, $start = 0, $limit = 5) {
        if ($id > 0) {
            if ($id > 1000000) {
                $id = $id - 1000000;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND `owner_id` = ".(int)$id." AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) LIMIT ".$start.", ".$limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        } else {
            if ($id < -2) {
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` ORDER BY `gid` ASC LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) LIMIT ".$start.", ".$limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` ORDER BY `gid` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $q = $this->db->query($sql);
        $jobs = $q->result();
        $return = array();
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            $user = & Users::getInstance();

            $jarr['userinfo'] = $user->getUserInfo($job->owner_id);

            $return[] = $jarr;
        }
        return $return;
    }

    public function getOverduePriceListDetails($id = 0, $start = 0, $limit = 5) {
        if ($id > 0) {
            if ($id > 1000000) {
                $id = $id - 1000000;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND `owner_id` = ".(int)$id." AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) LIMIT ".$start.", ".$limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND `owner_id` = " . (int) $id . " AND NOW() > `end_date` ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        } else {
            if ($id < -2) {
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` ORDER BY `start_date` ASC LIMIT " . $start . ", " . $limit;
            } else {
                //$sql = "SELECT *, ADDDATE(`end_date`, INTERVAL 7 DAY) AS `duedate` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) LIMIT ".$start.", ".$limit;
                $sql = "SELECT *, ADDDATE(`end_date`, INTERVAL `pterms` DAY) AS `duedate` FROM `job_group` WHERE ((`status`>1 AND `status` < 7) OR `status` = 14) AND NOW() > `end_date` ORDER BY `start_date` DESC LIMIT " . $start . ", " . $limit;
            }
        }
        $q = $this->db->query($sql);
        $jobs = $q->result();
        $return = array();
        foreach ($jobs as $job) {
            $routes = $this->getOrderJobs($job->gid);
            $jarr = array('order' => $job, 'routes' => $routes);

            $user = & Users::getInstance();

            $jarr['userinfo'] = $user->getUserInfo($job->owner_id);

            $return[] = $jarr;
        }
        return $return;
    }

    public function getDueCount($id = 0) {

        if ($id > 0) {
            $userId = " AND `owner_id` = " . (int) $id;
        } else {
            $userId = "";
        }
        //$sql = "SELECT COUNT(*) AS `total` FROM `job_group` WHERE `status` < 7 AND `status` > 1 ".$userId." AND (NOW() BETWEEN ADDDATE(`end_date`, INTERVAL 7 DAY) AND ADDDATE(`end_date`, INTERVAL 14 DAY))";
        //$sql = "SELECT `end_date` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) ".$userId." AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0";

        $sql = "SELECT `end_date` FROM `job_group` WHERE ((`status` > 1 AND `status` < 7) OR `status` = 14) " . $userId . " AND NOW() > `end_date` AND NOW() < ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `gid` DESC";



        $q = $this->db->query($sql);
        //$res = $q->row();		
        //return $res->total;

        $res = $q->result();
        return count($res);
    }

    public function getOverdueCount($id = 0) {
        if ($id > 0) {
            $userId = " AND `owner_id` = " . (int) $id;
        } else {
            $userId = "";
        }

        $sql = "SELECT `end_date` FROM `job_group` WHERE (`status` < 7 OR `status` = 14) " . $userId . " AND NOW() > ADDDATE(`end_date`, INTERVAL {$this->_config['number_of_overdue_days']} DAY) AND (`price`-`paid`) > 0 ORDER BY `gid` DESC";

        $q = $this->db->query($sql);
        $res = $q->result();

        return count($res);
    }

    public function changeStatus($id, $status) {
        $data = array('status' => $status);

        $this->db->where('gid', $id);
        $this->db->update('job_group', $data);
    }

    public function changeJobStatus($jobId, $status) {
        $data = array('job_status' => $status);

        $this->db->where('id', $jobId);
        $this->db->update('jobs', $data);
    }

    public function cancelOrder($id) {
        $data = array('cancel' => 1);

        $this->db->where('gid', $id);
        $this->db->update('job_group', $data);

        $sql = "SELECT * FROM `job_group` WHERE `gid` = " . $id;
        $query = $this->db->query($sql);
        $job = $query->row();

        $today = time();
        $pa = strtotime($job->start_date) - $today;
        if ($pa > 86400) {
            $fee = 0;
            $this->setCancelFee($id, $fee);
        }
    }

    public function setCancelFee($id, $fee) {
        if ($fee > 0) {
            $data = array('cancelfee' => $fee);

            $this->db->where('gid', $id);
            $this->db->update('job_group', $data);
        } else {
            $this->changeStatus($id, 8);

            $data = array('cancelfee' => 0);

            $this->db->where('gid', $id);
            $this->db->update('job_group', $data);
        }
    }

    public function setCancelPaid($id) {
        $data = array('cancelfeepaid' => 1);

        $this->db->where('gid', $id);
        $this->db->update('job_group', $data);
    }

    public function neededPayment($jid) {
        $sql = "SELECT * FROM `job_group` WHERE `gid` = " . $jid;
        $query = $this->db->query($sql);
        $job = $query->row();

        $total = ($job->price) + ($job->price * $job->tax / 100) - ($job->price * $job->discount / 100);

        if ($job->status < 4) { // new job, first payment
            /* if ($total > 500) {
              return $total/2;
              } else {
              return 0;
              } */
            return $job->preq;
        } else { // second payment, after job completion
            $needed = $total - $job->paid;
            $needed = ($needed < 0) ? 0 : $needed;

            return $needed;
        }
    }

    public function addPayment($jid, $amount) {
        $sql = "UPDATE `job_group` SET `paid` = (`paid` + " . $amount . ") WHERE `gid` = " . $jid;
        $this->db->query($sql);
    }

    /**
     * Adds the job order to the database
     * Type:
     * 0 - trip based
     * 1 - time based
     * 2 - term based
     * 3 - upload itinerary
     * 
     * @param array/object $params
     */
    public function createJob($params) {
        $params['create_date'] = date("Y-m-d H:i:s", time());
        $params['sessionid'] = $this->session->userdata('session_id');

        $this->db->insert('jobs', $params);
        return $this->db->insert_id();
    }

    public function updateTrip($date, $passangers, $from, $to, $return, $moreinfo, $owner, $draft = 0, $return_time = 0) {
        $startDate = date('Y-m-d H:i:s', strtotime($date));

        $returnTime = date('Y-m-d H:i:s', strtotime($return_time));

        $data = array(
            'start_date' => $startDate,
            'passangers' => $passangers,
            'from' => $from,
            'to' => $to,
            'return' => $return,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 0,
            'owner_id' => $owner,
            'return_time' => $returnTime
        );


        $this->db->where('id', $editId);
        $this->db->update('jobs', $data);

        return $data;
    }

    public function createTrip($date, $passangers, $from, $to, $return, $moreinfo, $owner, $draft = 0, $editId = 0, $return_time = 0) {
        $data = array(
            'start_date' => $date,
            'passangers' => $passangers,
            'from' => $from,
            'to' => $to,
            'return' => $return,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 0,
            'owner_id' => $owner,
            'return_time' => $return_time
        );

        if ($editId > 0) {
            $this->db->where('id', $editId);
            $this->db->update('jobs', $data);

            return $editId;
        }

        return $this->createJob($data);
    }

    public function updateTime($startdate, $enddate, $passangers, $hours, $locations, $moreinfo, $owner, $draft = 0, $editId = 1) {
        $startDate = date('Y-m-d H:i:s', strtotime($startdate));

        $endDate = date('Y-m-d H:i:s', strtotime($enddate));

        $data = array(
            'start_date' => $startDate,
            'end_date' => $endDate,
            'passangers' => $passangers,
            'hours' => $hours,
            'locations' => $locations,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 1,
            'owner_id' => $owner
        );

        if ($editId > 0) {
            $this->db->where('id', $editId);
            $this->db->update('jobs', $data);

            return $data;
        }

        return $this->createJob($data);
    }

    public function createTime($date, $enddate, $passangers, $hours, $locations, $moreinfo, $owner, $draft = 0, $editId = 0) {
//            echo $owner;die;
        $data = array(
            'start_date' => $date,
            'end_date' => $enddate,
            'passangers' => $passangers,
            'hours' => $hours,
            'locations' => $locations,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 1,
            'owner_id' => $owner
        );

        if ($editId > 0) {
            $this->db->where('id', $editId);
            $this->db->update('jobs', $data);

            return $editId;
        }

        return $this->createJob($data);
    }

    public function createTerm($date, $moreinfo, $owner, $draft = 0) {
        $data = array(
            'start_date' => $date,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 2,
            'owner_id' => $owner
        );

        return $this->createJob($data);
    }

    public function createUpload($filename, $ext, $filesize, $origname, $moreinfo, $owner, $draft = 0) {
        $data = array(
            'filename' => $filename,
            'ext' => $ext,
            'filesize' => $filesize,
            'origname' => $origname,
            'origid' => $owner,
            'moreinfo' => $moreinfo,
            'draft' => $draft,
            'job_type' => 3,
            'owner_id' => $owner
        );

        return $this->createJob($data);
    }

    public function formatPrice($price, $sign = true) {
        if ($sign)
            return 'S$ ' . number_format($price, 2, '.', ',');
        else
            return number_format($price, 2, '.', ',');
    }

    /**
     * Load jobs
     * Type:
     * all - all jobs
     * 0-3 - choosen type
     * 4 - time and trip based
     * 
     * @param $type
     */
    public function getJobs($uid, $type = 'all') {
        if ($type != 'all') {
            if ($type == 4) {
                $addToSql = " AND (`job_type` = 0 OR `job_type` = 1)";
            } elseif ($type >= 0 && $type <= 3) {
                $addToSql = " AND `job_type` = " . $type;
            } else {
                $addToSql = "";
            }
        } else {
            $addToSql = "";
        }

        if ($uid == '0')
            $addToSql = " AND `sessionid`='" . $this->session->userdata('session_id') . "'";

//		$sql = "SELECT *, `return` as `returnn`, YEAR(`start_date`) AS `y`, MONTH(`start_date`) AS `m`, DAY(`start_date`) AS `d`, HOUR(`start_date`) AS `h`, MINUTE(`start_date`) AS `mi`, YEAR(`end_date`) AS `ey`, MONTH(`end_date`) AS `em`, DAY(`end_date`) AS `ed`, HOUR(`end_date`) AS `eh`, MINUTE(`end_date`) AS `emi` FROM `jobs` WHERE `owner_id` = ".$uid." AND `sessionid` = '".$this->session->userdata('session_id')."'".$addToSql." AND `belongs` = 0 ORDER BY `id` DESC";
        $sql = "SELECT *, `return` as `returnn`, YEAR(`start_date`) AS `y`, MONTH(`start_date`) AS `m`, DAY(`start_date`) AS `d`, HOUR(`start_date`) AS `h`, MINUTE(`start_date`) AS `mi`, YEAR(`end_date`) AS `ey`, MONTH(`end_date`) AS `em`, DAY(`end_date`) AS `ed`, HOUR(`end_date`) AS `eh`, MINUTE(`end_date`) AS `emi` FROM `jobs` WHERE `owner_id` = " . $uid . $addToSql . " AND `belongs` = 0 ORDER BY `id` DESC";
        $query = $this->db->query($sql);
        $jobs = $query->result();

        /* for ($i = 0; $i < count($jobs); $i++) {
          if ($jobs[$i]->job_type == "1") {
          unset($jobs[$i]->locations);
          }
          } */

        return $jobs;
    }

    public function getJob($jid, $format = true) {
        $sql = "SELECT *, `return` as `returnn`, 
		YEAR(`start_date`) AS `y`, MONTH(`start_date`) AS `m`, DAY(`start_date`) AS `d`, 
		HOUR(`start_date`) AS `h`, MINUTE(`start_date`) AS `mi`, 
		YEAR(`end_date`) AS `ey`, MONTH(`end_date`) AS `em`, DAY(`end_date`) AS `ed`, 
		HOUR(`end_date`) AS `eh`, MINUTE(`end_date`) AS `emi`,
		YEAR(`return_time`) AS `ry`, MONTH(`return_time`) AS `rm`, DAY(`return_time`) AS `rd`, 
		HOUR(`return_time`) AS `rh`, MINUTE(`return_time`) AS `rmi` 
		FROM `jobs` WHERE `id` = " . $jid;
        $query = $this->db->query($sql);

        $res = $query->row();
        if ($format)
            $res->{"formatted"} = $this->formatJobInfo($res);

        /* if ($res->job_type == "1") {
          unset($res->locations);
          } */

        return $res;
    }

    public function delete($jid, $uid = null) {
        $data = array("id" => $jid);
        if ($uid != null) {
            $data['owner_id'] = $uid;
        }
        $this->db->delete('jobs', $data);
    }

    public function changePrice($id, $reason, $amount) {
        $sql = "UPDATE `job_group` SET `price` = `price`" . $amount . " WHERE `gid` = " . $id;
        $this->db->query($sql);



        $sql = "SELECT * FROM `conversation` WHERE `jid`= " . $id . " AND `uid` = '1'";
        $elapse = $this->db->query($sql);
        $trips = $elapse->result();
        $count_elapse = count($trips);
        if ($count_elapse > 0) {
            for ($i = 0; $i < $count_elapse; $i++) {
                $tr = $trips[$i];
                $s = $tr->text;
                $cid = $tr->id;
                $strings = json_encode($s);
                $strings = str_replace('<p>You can view or print the quotation', 'daniel<p>You can view or print the quotation', $strings);
                $strings = str_replace('<p>Job ID is used as Invoice number', 'daniel<p>Job ID is used as Invoice number', $strings);
                $st = explode('daniel', $strings);
                if ($st[2]) {
                    $strings = $st[0] . $st[2];
                }
                $strings = str_replace('\t', '	', $strings);
                $strings = str_replace('\n', '
	', $strings);
                $strings = str_replace('\/', '/', $strings);
                $sql = "UPDATE `conversation` SET `text` = " . $strings . " WHERE `jid` = " . $id . " AND `uid` = '1' AND `id` = " . $cid;
                $this->db->query($sql);
            }
        }




        $data = array(
            'jid' => $id,
            'reason' => $reason,
            'amount' => $amount
        );
        $this->db->insert('price_changes', $data);
    }

    public function getPrice($id) {
        $sql = "SELECT * FROM `job_group` WHERE `gid` = " . $id;
        $query = $this->db->query($sql);

        return $query->row();
    }

    public function formatJobInfo($job, $showType = false) {
        $text = "";

        if ($job->job_type == 0) {
            // trip
            if ($showType)
                $text .= "Type: Trip Based\n";
            $text .= "Trip Date and Time: " . $job->start_date;
            $text .= "\nNumber of Passengers: " . $job->passangers;
            $text .= "\nFrom: " . htmlspecialchars($job->from);
            //$text .= "\nTo: ".htmlspecialchars($job->to);
            $locs = json_decode($job->to);
            $text .= "\nTo: ";
            if (is_array($locs)) {
                $iter = 0;
                foreach ($locs as $loc) {
                    if ($iter > 0)
                        $text .= "; ";
                    $text .= htmlspecialchars($loc);

                    $iter++;
                }
            }

            if ($job->total_price != 0)
                $text .= "\nPrice: " . $job->total_price;

            if ($job->bus_type != NULL)
                $text .= "\nBus type: " . htmlspecialchars($job->bus_type);

            if (!empty($job->return)) {
                $text .= "\nReturn: " . htmlspecialchars($job->return);
                $text .= "\nReturn Time: " . $job->return_time;
            }
        } elseif ($job->job_type == 1) {
            // time
            if ($showType)
                $text .= "Type: Time Based\n";
            $text .= "Start Date and Time: " . $job->start_date;
            $text .= "\nEnd Date and Time: " . $job->end_date;
            $text .= "\nNumber of Passengers: " . $job->passangers;
            $text .= "\nNumber of Hours: " . $job->hours;
            $locs = json_decode($job->locations);
            $text .= "\nLocations: ";
            if (is_array($locs)) {
                $iter = 0;
                foreach ($locs as $loc) {
                    if ($iter > 0)
                        $text .= "; ";
                    $text .= htmlspecialchars($loc);
                    $iter++;
                }
            }
            if ($job->total_price != 0)
                $text .= "\nPrice: S$" . $job->total_price;

            if ($job->bus_type != NULL)
                $text .= "\nBus type: " . htmlspecialchars($job->bus_type);
        } elseif ($job->job_type == 2) {
            // term
            $date = explode(" ", $job->start_date);
            $text .= "Expected start date: " . $date[0];
        } elseif ($job->job_type == 3) {
            // itinerary
            $text .= anchor('/download/' . $job->id, $job->origname);
        }

        return $text;
    }

    public function elapsed_date($jobgroup) {
        $text = "";
        $text .= "\nElapsed Date: " . $jobgroup->elapsed_date;
        return $text;
    }

    public function formatJobInfoNoPrice($job, $showType = false) {
        $text = "";

        if ($job->job_type == 0) {
            // trip
            if ($showType)
                $text .= "Type: Trip Based\n";
            $text .= "Trip Date and Time: " . $job->start_date;
            $text .= "\nNumber of Passengers: " . $job->passangers;
            $text .= "\nFrom: " . htmlspecialchars($job->from);
            //$text .= "\nTo: ".htmlspecialchars($job->to);
            $locs = json_decode($job->to);
            $text .= "\nTo: ";
            if (is_array($locs)) {
                $iter = 0;
                foreach ($locs as $loc) {
                    if ($iter > 0)
                        $text .= "; ";
                    $text .= htmlspecialchars($loc);

                    $iter++;
                }
            }

            if ($job->bus_type != NULL)
                $text .= "\nBus type: " . htmlspecialchars($job->bus_type);

            if (!empty($job->return)) {
                $text .= "\nReturn: " . htmlspecialchars($job->return);
                $text .= "\nReturn Time: " . $job->return_time;
            }
        } elseif ($job->job_type == 1) {
            // time
            if ($showType)
                $text .= "Type: Time Based\n";
            $text .= "Start Date and Time: " . $job->start_date;
            $text .= "\nEnd Date and Time: " . $job->end_date;
            $text .= "\nNumber of Passengers: " . $job->passangers;
            $text .= "\nNumber of Hours: " . $job->hours;
            $locs = json_decode($job->locations);
            $text .= "\nLocations: ";
            if (is_array($locs)) {
                $iter = 0;
                foreach ($locs as $loc) {
                    if ($iter > 0)
                        $text .= "; ";
                    $text .= htmlspecialchars($loc);

                    $iter++;
                }
            }

            if ($job->bus_type != NULL)
                $text .= "\nBus type: " . htmlspecialchars($job->bus_type);
        } elseif ($job->job_type == 2) {
            // term
            $date = explode(" ", $job->start_date);
            $text .= "Expected start date: " . $date[0];
        } elseif ($job->job_type == 3) {
            // itinerary
            $text .= anchor('/download/' . $job->id, $job->origname);
        }

        return $text;
    }

    private function _getcancelurl($uri = '', $anchor_attributes = '', $img_src = '', $img_attributes = '') {
        if (!is_array($uri)) {
            $site_url = (!preg_match('!^\w+://! i', $uri)) ? site_url($uri) : $uri;
        } else {
            $site_url = site_url($uri);
        }

        if ($anchor_attributes != '') {
            $anchor_attributes = _parse_attributes($anchor_attributes);
        }

        if (strpos($img_src, '://') === FALSE) {
            $CI = & get_instance();
            $img_src = $CI->config->slash_item('base_url') . $img_src;
        }

        if ($img_attributes != '') {
            $img_attributes = _parse_attributes($img_attributes);
        }

        //return '<a href="javascript:confirmCancel(\''.$site_url.'\')"'.$anchor_attributes.'>'.'<img src="'.$img_src.'" />'.'</a>';
        return '<a href="' . $uri . '"' . $anchor_attributes . '>' . '<img src="' . $img_src . '" />' . '</a>';
    }

    /**
     * Job order
     * Status:
     * 0 - awaiting quotation
     * 1 - quoted a price
     * 2 - user accepted
     * 3 - user paid
     * 4 - payment accepted
     * 5 - job done, waiting for payment
     * 6 - job done, paid everything
     * 7 - job done, payment accepted, job closed
     */
    function showActionLinks($job, $where) {
        $html = '';

        if ($where == "oncoming") {
            if ($job->status == 0) {
                if ($job->cancel != 1) {
                    $html .= $this->_getcancelurl('joborder/deny/' . $job->gid, 'title="Cancel this job order"', 'assets/images/icons/delete.png');
                    if ($job->type == 2) {
                        $html .= ' ' . anchor_img('/request/term-based/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    } elseif ($job->type == 3) {
                        $html .= ' ' . anchor_img('/request/upload-itinerary/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    } else {
                        $html .= ' ' . anchor_img('/request/time-based/0/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    }
                }
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } elseif ($job->status == 1) {
                if ($job->cancel != 1) {
                    $html .= anchor_img('joborder/accept/' . $job->gid, 'title="Accept!"', 'assets/images/icons/yes.png');
                    $html .= ' ' . anchor_img('joborder/deny/' . $job->gid, 'title="Deny!"', 'assets/images/icons/no.png');
                    if ($job->type == 2) {
                        $html .= ' ' . anchor_img('/request/term-based/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    } elseif ($job->type == 3) {
                        $html .= ' ' . anchor_img('/request/upload-itinerary/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    } else {
                        $html .= ' ' . anchor_img('/request/time-based/0/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
                    }
                }
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } elseif ($job->status == 2) {
                if ($job->cancel != 1) {
                    $html .= $this->_getcancelurl('joborder/cancel/' . $job->gid, 'title="Cancel this job order"', 'assets/images/icons/delete.png');
                    //$html .= ' '.anchor_img("joborder/".$job->gid."#actionForm", 'title="Pay"', 'assets/images/icons/pay.png');
                }
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } elseif ($job->status == 3) {
                if ($job->cancel != 1) {
                    $html .= $this->_getcancelurl('joborder/cancel/' . $job->gid, 'title="Cancel this job order"', 'assets/images/icons/delete.png');
                }
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } elseif ($job->status == 4 || $job->status == 14) {
                if ($job->cancel != 1) {
                    $html .= $this->_getcancelurl('joborder/cancel/' . $job->gid, 'title="Cancel this job order"', 'assets/images/icons/delete.png');
                }
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Pay"', 'assets/images/icons/pay.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } else {
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            }
        } elseif ($where == "drafts") {
            $html .= anchor_img("joborder/reuse/" . $job->gid, 'title="Reuse Job info and edit it to submit new request"', 'assets/images/icons/again.png');
            $html .= anchor_img('joborder/delete/' . $job->gid, 'title="Delete draft"', 'assets/images/icons/delete.png');
            if ($job->type == 2) {
                $html .= ' ' . anchor_img('/request/term-based/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
            } elseif ($job->type == 3) {
                $html .= ' ' . anchor_img('/request/upload-itinerary/' . $job->gid, 'title="Edit"', 'assets/images/icons/edit.png');
            } else {
                $html .= ' ' . anchor_img('/request/time-based/0/' . $job->gid, 'title="Edit draft"', 'assets/images/icons/edit.png');
            }
        } elseif ($where == "due" || $where == "overdue") {
            $html .= anchor_img("joborder/reuse/" . $job->gid, 'title="Reuse Job info and edit it to submit new request"', 'assets/images/icons/again.png');
            $html .= anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Pay"', 'assets/images/icons/pay.png');
        } elseif ($where == "completed") {
            $html .= anchor_img("joborder/reuse/" . $job->gid, 'title="Reuse Job info and edit it to submit new request"', 'assets/images/icons/again.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
        } elseif ($where == "cancelled") {
            $html .= anchor_img("joborder/reuse/" . $job->gid, 'title="Reuse Job info and edit it to submit new request"', 'assets/images/icons/again.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
        }

        return $html;
    }

    /**
     * Job order
     * Status:
     * 0 - awaiting quotation
     * 1 - quoted a price
     * 2 - user accepted
     * 3 - user paid
     * 4 - payment accepted
     * 5 - job done, waiting for payment
     * 6 - job done, paid everything
     * 7 - job done, payment accepted, job closed
     */
    function showAdminActionLinks($job, $where) {
        $user = & Users::getInstance();

        // operation admin
        if ($user->level == 2) {
            if ($job->status < 7) {
                $html = anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
                $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            } else {
                $html = anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            }

            return $html;
        }

        $html = '';

        if ($where == "oncoming") {
            $html .= anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
        } elseif ($where == "quote") {
            $html .= anchor_img('joborder/admdelete/' . $job->gid, 'title="Delete this job order"', 'assets/images/icons/delete.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
            if ($job->type == "2") {
                $html .= ' ' . anchor_img("ri/add/" . $job->gid, 'title="Transform"', 'assets/images/icons/transform.png');
            } elseif ($job->type == "3") {
                $html .= ' ' . anchor_img("request/time-based/0/" . $job->gid, 'title="Transform"', 'assets/images/icons/transform.png');
            } else {
                $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Quote"', 'assets/images/icons/quote.png');
            }
        } elseif ($where == "confirm") {
            $html .= anchor_img('joborder/admdelete/' . $job->gid, 'title="Delete this job order"', 'assets/images/icons/delete.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
            $html .= ' ' . anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
        } elseif ($where == "due") {
            $html .= anchor_img("joborder/" . $job->gid . "#actionForm", 'title="Send message"', 'assets/images/icons/contact.png');
        } elseif ($where == "completed") {
            $html .= anchor_img("joborder/" . $job->gid, 'title="View details"', 'assets/images/icons/view.png');
        }

        return $html;
    }

    function getID($job) {
        if (is_object($job)) {
            $id = $job->gid;
            $date = explode(" ", $job->create_date);
            $date = $date[0];
            $date = str_replace("-", "", $date);
        } else {
            $id = $job;
            $date = date("Ymd");
        }

        $format = $date . "-" . str_pad($id, 4, "0", STR_PAD_LEFT);

        return $format;
    }

    function deleteJobOrder($jid) {
        $data = array('gid' => $jid);
        $this->db->delete('job_group', $data);

        $data = array('belongs' => $jid);
        $this->db->delete('jobs', $data);
    }

    function duplicateJobs($jid) {
        $sql = "INSERT INTO `jobs` (`start_date`,`end_date`, `passangers`, `from`, `to`, `return`,`return_time`, `hours`, `locations`, `filename`, `ext`, `filesize`, `origname`, `origid`, `moreinfo`, `owner_id`, `sessionid`, `create_date`, `job_type`, `job_status`, `payment_due_date`, `payment_overdue`, `draft`) (SELECT `start_date`,`end_date`, `passangers`, `from`, `to`, `return`,`return_time`, `hours`, `locations`, `filename`, `ext`, `filesize`, `origname`, `origid`, `moreinfo`, `owner_id`, '" . $this->session->userdata('session_id') . "', NOW(), `job_type`, '0', '0', '0', `draft` FROM `jobs` WHERE `belongs` = " . $jid . ")";
        $this->db->query($sql);
    }

    function updateJobGroup($jid) {
		$end_date = date('Y-m-d H:i:s', time());
        $sql = "UPDATE `job_group` SET `status`=4, `elapsed_date`= 0, `duration`=0, `end_date`='$end_date', `invalid`='$end_date', `start_date`='$end_date', `draft`=0 WHERE `gid` = " . $jid;
        $this->db->query($sql);
    }

    public function addInvoice($jid, $type, $fields, $proforma = 0) {
        $this->load->model('Chats');
        $user = & Users::getInstance();

        $partial = 0;

        if (!$proforma) {
            $jobInfo = $this->Jobs->getOrderJob($jid);

            $pneed = $this->neededPayment($jid);

            if ($pneed > 0 && $jobInfo->paid <= 0)
                $partial = 1;
        }

        $idata = array(
            'jid' => $jid,
            'type' => $type,
            'date' => date('Y-m-d'),
            'partial' => $partial,
            'fields' => $fields,
            'proforma' => $proforma
        );

        $this->db->insert('ainvoices', $idata);

        $inv = $this->db->insert_id();

        if (!$proforma) {
            $this->Jobs->setMessage($jid, 1, 0);

            $text = '<p>We have issued a new invoice on your order. You can see the invoice ' . anchor('/invoices/view/' . $inv, 'here') . '</p>';

            $this->Chats->post($jid, $user->id, $text, 6, 0, 0);
        }

        return $inv;
    }

    public function getInvoices($proforma = false) {
        $user = Users::getInstance();

        if ($proforma) {
            $this->db->where('proforma', 1);
        } else {
            $this->db->where('proforma !=', 1);
        }
        $this->db->where('type !=', 1);
        $this->db->order_by("date", "desc");
        $invoices = $this->db->get('ainvoices');
        $invoices = $invoices->result();

        foreach ($invoices as $k => $invoice) {
            $jobInfo = $this->getOrderJob($invoice->jid);
            if ($jobInfo) {
                $uid = $jobInfo->owner_id;
                $invoices[$k]->uinfo = $user->getUserInfo($uid, false);

                $type = $invoice->type;
                $grandtotal = $jobInfo->price + ($jobInfo->price * $jobInfo->tax / 100) - ($jobInfo->price * $jobInfo->discount / 100);
                if ($type == 2) { // cancellation fee
                    $invoices[$k]->amount = $jobInfo->cancelfee;
                    $paid = $jobInfo->paid;
                    $invoices[$k]->unpaid = $grandtotal - $paid;
                    $invoices[$k]->grandtotal = $grandtotal;
                } else {
                    if ($type == 1) {
                        $invoices[$k]->amount = $jobInfo->price + ($jobInfo->price * $jobInfo->tax / 100) - ($jobInfo->price * $jobInfo->discount / 100);
                        $paid = $jobInfo->paid;
                        $invoices[$k]->unpaid = $grandtotal - $paid;
                        $invoices[$k]->grandtotal = $grandtotal;
                    } else {
                        $total = $jobInfo->price;
                        $grandtotal = $jobInfo->price + ($jobInfo->price * $jobInfo->tax / 100) - ($jobInfo->price * $jobInfo->discount / 100);
                        $paid = $jobInfo->paid;
                        $invoices[$k]->unpaid = $grandtotal - $paid;
                        $invoices[$k]->grandtotal = $grandtotal;
                        if ($invoice->partial == 1) {
                            $invoices[$k]->amount = $jobInfo->preq;
                        } else {
                            $invoices[$k]->amount = $grandtotal - $paid;
                        }
                    }
                }
            }
        }

        return $invoices;
    }

    function createPDF($uinfo, $order, $type, $date, $invoice = null) {
        $ptermText = "COD";
        if ($order->pterms == "0" || $order->pterms == "") {
            $ptermText = "COD";
        } else if ($order->pterms == "1") {
            $ptermText = "Pre-pay";
        } else {
            $ptermText = $order->pterms . " " . "Days";
        }
        if ($invoice) {
            $type = $invoice->type;
            $date = $invoice->date;
        }

        $strPmethod = "Cash, Bank";
        if ($order->pmethods) {
            $arr = json_decode($order->pmethods);
            foreach ($arr as $k => &$v) {
                $v = ucfirst($v);
            }
            $strPmethod = implode(", ", $arr);
        }
        require_once(FCPATH . '/tcpdf/config/lang/eng.php');
        require_once(FCPATH . '/tcpdf/tcpdf.php');

        // create new PDF document
        //$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
        $width = 500;
        $height = 300;
        $orientation = ($height > $width) ? 'P' : 'L';
        /* $pdf->addFormat("custom", $width, $height);  
          $pdf->reFormat("custom", $orientation); */
        $pagelayout = array($width, $height);
        $pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, $pagelayout, true, 'UTF-8', false);

        // set document information
        $pdf->SetCreator(PDF_CREATOR);
        $pdf->SetAuthor('BookaBus Ltd.');
        $pdf->SetTitle('Invoice');
        $pdf->SetSubject('Invoice');
        $pdf->SetKeywords('BookaBus');

        // set header and footer fonts
        $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
        $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

        // set default monospaced font
        $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

        //set margins
        $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
        $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
        $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

        //set auto page breaks
        $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

        //set image scale factor
        $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

        //set some language-dependent strings
        $pdf->setLanguageArray($l);

        // ---------------------------------------------------------
        // set font
        $pdf->SetFont('dejavusans', 'B', 10);

        $pdf->AddPage();

        $clientname = $uinfo->name;
        if (!empty($uinfo->company))
            $clientname .= ", " . $uinfo->company;

        $validity = "";
        $terms = "";

        if ($type == 1)
            $terms = '<b>TERMS:</b> ' . $order->period . ' months<br  />';
        else
            $validity = $order->invalid;

        if ($type == 0 || $type == 2)
            $order->id = $order->gid;

        $fieldsHTML = '';
        if (!empty($invoice->fields)) {
            $fields = json_decode($invoice->fields);

            foreach ($fields as $field) {
                if (strlen($field->name) > 0)
                    $fieldsHTML .= '<b>' . $field->name . ':</b> ' . $field->value . '<br  />';
            }
        }

        if ($type == 1) {
            $jobId = $order->id;
        } else {
            $jobId = $this->Jobs->getID($order);
        }
        if ($invoice->proforma == 1) {
            $html = '<h1 align="center"><img src="' . FCPATH . 'assets/images/bookabus.jpg" /></h1>				
					<table style="border-collapse: collapse;" valign="top" cellpadding="0" cellspacing="0" border="0" bordercolor="#CCCCCC" align="left" width="100%">	
						<tr>
							<td valign="top" style="width:100"><b>ATTN:</b></td>
							<td valign="top" style="width:400"></td>
							<td valign="top" style="width:160"><b>FROM:</b></td>
							<td valign="top" style="width:400"></td>
						</tr>
						<tr style="height:19px;">
							<td valign="top"><b>TO:</b></td>
							<td valign="top">' . $clientname . '</td>
							<td valign="top"><b>REP:</b></td>
							<td valign="top">Darren Lim<br/>
											Mobile: 94773707<br/>
											E-mail: darren@bookabus.sg</td>
						</tr>
						<tr style="height:19px;">
							<td valign="top"></td>
							<td valign="top">' . $uinfo->company . '<br/>' . $uinfo->address . '</td>
							<td valign="top"><b>ADDRESS:</b></td>
							<td valign="top">20 Bukit Batok Crescent<br/>
											#09-07 Enterprise Centre<br/>
											Singapore 658080</td>
						</tr>
						<tr>
							<td valign="top"><b>TEL:</b></td>
							<td valign="top">' . $uinfo->phone . '</td>
							<td valign="top"><b>TEL:</b></td>
							<td valign="top">+65 6600 1178</td>
						</tr>
						<tr>
							<td valign="top"><b>FAX:</b></td>
							<td valign="top"></td>
							<td valign="top"><b>FAX:</b></td>
							<td valign="top">+65 6569 6166</td>
						</tr>
						<tr>
							<td valign="top"><b>EMAIL:</b></td>
							<td valign="top">' . $uinfo->email . '</td>
							<td valign="top"><b>EMAIL:</b></td>
							<td valign="top"></td>
						</tr>
						<tr>
							<td valign="top"><b>For:</b></td>
							<td valign="top">Bus Services</td>
							<td valign="top"><b>Quote Reference:</b></td>
							<td valign="top">' . $jobId . '</td>
						</tr>
						<tr>
							<td valign="top"><b>Date:</b></td>
							<td valign="top">' . date('jS F Y', strtotime($date)) . '</td>
							<td valign="top"><b>Validity :</b></td>
							<td valign="top">' . $validity . '</td>
						</tr>
						<tr>
							<td valign="top"></td>
							<td valign="top"></td>
							<td valign="top"><b>Terms:</b></td>
							<td valign="top">' . $terms . '</td>
						</tr>
						<tr>
							<td></td>
							<td></td>
							<td><b>Payment terms:</b></td>
							<td>' . $ptermText . '</td>
						</tr>
						<tr>
							<td></td>
							<td></td>
							<td><b>Payment methods:</b></td>
							<td>' . $strPmethod . '<br/>' . $fieldsHTML . '</td>
						</tr>
					</table>
					<br/>
					<br/>
					<h1 align="center">' . (($invoice->proforma == 1) ? 'Quotation' : 'Invoice') . '</h1>
				
				
				<br />

				<table  style="border: 1px solid #09C; border-collapse: collapse;" valign="top" cellpadding="10" cellspacing="0" border="0" bordercolor="#CCCCCC" align="left" width="100%">
					<tr bgcolor="#c3ebff">
						<td valign="top" width="80px" style="border-top: 1px solid #09C; border-left: 1px solid #09C;"><b>S/N</b></td>
						<td valign="top" width="540px" style="border-top: 1px solid #09C; "><b>Description</b></td>
						<td valign="top" width="80px" style="border-top: 1px solid #09C; "><b>Pax</b></td>
						<!--<td valign="top" style="border-top: 1px solid #09C; "><b>' . (($type == 1) ? "DATE (TIME)" : "TYPE") . '</b></td>-->
						<td valign="top" width="150px" style="border-top: 1px solid #09C; "><b>Bus Type</b></td>
						<td valign="top" width="110px" style="border-top: 1px solid #09C; border-right: 1px solid #09C;"><b>' . (($type == 1) ? "Amount, per month (SGD)" : 'Amount (SGD)') . '</b></td>
					</tr>';
            if ($type == 1) {
                //recurring
                $routes = json_decode($order->routes);
                $c = 1;
                foreach ($routes as $route) {
                    for ($i = 0; $i < count($route->weekdays); $i++) {
                        $route->weekdays[$i] = substr($route->weekdays[$i], 0, 2);
                    }
                    $html .= '<tr style="border-top: 1px solid #09C; ">
								<td valign="top" style="border-top: 1px solid #09C; ">' . $c . '</td>
								<td valign="top" style="border-top: 1px solid #09C; ">' . implode(', ', $route->weekdays) . '</td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; ">' . $route->bustype . '</td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
							</tr>';

                    // locations
                    foreach ($route->pickup as $pickup) {
                        $html .= '<tr style="border-top: 1px solid #09C; ">
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; ">Pickup Location: ' . $pickup->location . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . $pickup->time . '</td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
								</tr>';
                    }
                    foreach ($route->dropoff as $dropoff) {
                        $html .= '<tr style="border-top: 1px solid #09C; ">
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; ">Drop Off Location: ' . $dropoff->location . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . $dropoff->time . '</td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
								</tr>';
                    }

                    $html .= '<tr style="border-top: 1px solid #09C; ">
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; ">Total (Item ' . $c . ')</td>
								<td valign="top" style="border-top: 1px solid #09C; ">' . number_format($route->price, 2, '.', ',') . '</td>
							</tr>';

                    $c++;
                }

                $html .= '<tr style="border-top: 1px solid #09C;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #09C; border-left: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>Grand Total:</b></td>
							<td valign="top" style="border-top: 1px solid #09C; border-right: 1px solid #09C; border-bottom: 1px solid #09C;"><b>' . number_format($order->total_price, 2, '.', ',') . '</b></td>
						</tr>';
            } elseif ($type == 2) { // cancellation fee
                $html .= '<tr style="border-top: 1px solid #09C; ">
								<td valign="top" style="border-top: 1px solid #09C; ">1</td>
								<td valign="top" style="border-top: 1px solid #09C; ">Cancelation fee</td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; "></td>
								<td valign="top" style="border-top: 1px solid #09C; ">' . number_format($order->cancelfee, 2, '.', ',') . '</td>
							</tr>';

                $html .= '<tr style="border-top: 1px solid #09C;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #09C; border-left: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>Grand Total:</b></td>
							<td valign="top" style="border-top: 1px solid #09C; border-right: 1px solid #09C; border-bottom: 1px solid #09C;"><b>' . number_format($order->cancelfee, 2, '.', ',') . '</b></td>
						</tr>';
            } else {
                // jobs
                $c = 0;
                $j = $this->getOrderJobs($order->gid);
                foreach ($j as $job) {
                    //                            echo "<pre>";print_r($job);die;
                    $c++;
                    if ($job->job_type == 0 || $job->job_type == 1) {
                        $totalPriceRow = $job->total_price;
                        if ($totalPriceRow == 0) {
                            $totalPriceRow = "--";
                        }
                        $html .= '<tr style="border-top: 1px solid #09C; ">
									<td valign="top" style="border-top: 1px solid #09C; ">' . $c . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . nl2br($this->Jobs->formatJobInfo($job)) . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . (($job->job_type == 1) ? "Time Based" : "Trip Based") . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . $job->bus_type . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . $totalPriceRow . '</td>
								</tr>';
                    }
                }

                // Price changes
                //if ($invoice->proforma != 1) {
                $this->db->where('jid', $order->gid);
                $qc = $this->db->get('price_changes');
                $changes = $qc->result();
                foreach ($changes as $change) {
                    $c++;
                    $reason = ($change->amount{0} == "-") ? "Price decrease: " : "Price increase: ";
                    $reason .= $change->reason;
                    $html .= '<tr style="border-top: 1px solid #09C; ">
									<td valign="top" style="border-top: 1px solid #09C; ">' . $c . '</td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . $reason . '</td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; "></td>
									<td valign="top" style="border-top: 1px solid #09C; ">' . number_format(str_replace(array('-', '+'), '', $change->amount), 2, '.', ',') . '</td>
								</tr>';
                }
                //}

                if ($type == 1) {
                    $html .= '<tr style="border-top: 1px solid #09C;  font-size: 45px;" bgcolor="#61c6d8">
						<td valign="top" style="border-top: 1px solid #09C; border-left: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
						<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
						<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
						<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>Grand Total:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; border-right: 1px solid #09C; border-bottom: 1px solid #09C;"><b>' . number_format($order->price + ($order->price * $order->tax / 100) - ($order->price * $order->discount / 100), 2, '.', ',') . '</b></td>
					</tr>';
                } else {
                    $total = $order->price;
                    $grandtotal = $order->price + ($order->price * $order->tax / 100) - ($order->price * $order->discount / 100);
                    $paid = $order->paid;
                    $html .= '<tr style="border-top: 1px solid #09C; ">
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>Total:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>' . number_format($total, 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #09C; ">
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>Tax:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>' . number_format(($order->price * $order->tax / 100), 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #09C; ">
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>Discount:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>-' . number_format(($order->price * $order->discount / 100), 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #09C;  font-size: 45px;" bgcolor="#61c6d8">
						<td valign="top" style="border-top: 1px solid #09C; border-left: 1px solid #09C;"></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>Grand Total:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; border-right: 1px solid #09C;"><b>' . number_format($grandtotal, 2, '.', ',') . '</b></td>
					</tr>';

                    if ($invoice->partial == 1) {
                        $html .= '<tr style="border-top: 1px solid #09C; ">
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>Required in advance:</b></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>' . number_format($order->preq, 2, '.', ',') . '</b></td>
						</tr>';
                    } else {
                        $html .= '<tr style="border-top: 1px solid #09C; ">
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>Already paid:</b></td>
						<td valign="top" style="border-top: 1px solid #09C; "><b>-' . number_format($paid, 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #09C;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #09C; border-left: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"></td>
							<td valign="top" style="border-top: 1px solid #09C; border-bottom: 1px solid #09C;"><b>Left to pay:</b></td>
							<td valign="top" style="border-top: 1px solid #09C; border-right: 1px solid #09C; border-bottom: 1px solid #09C;"><b>' . number_format($grandtotal - $paid, 2, '.', ',') . '</b></td>
						</tr>';
                    }
                }
            }
            $html .= '</table>';

            $signTable = '<br /><br />
			<table cellspacing="0" cellpadding="0" border="0" width="100%" style="padding-top:10px;">
				<tr>
					<td align="left" style="width:68%;">
						
					</td>
					<td align="left" style="width:31%;">
						Agreed & Accepted By:<br/><br/><br/><br /><br />
						____________________________<br/>
						Signature/Name/Company Stamp<br/>
						Date: ___________________<br />				
						Please sign/stamp and fax to: +65 6569 6166					
					</td>
				</tr>
				</table>';

            if ($type == 1) {
                $html .= '
	<p>Note:</p>
	<ol>
		<li>Quotation valid for 2 weeks subject to availability of Buses</li>
		<li>No GST Chargeable</li>
		<li>1 week notice required before commencement of Services to facilitate Driver Orientation</li>
	</ol>
	<h2 align="center">THANK YOU FOR YOUR CONSIDERATION</h2>';

                $html .= $signTable;
            } else {
                if (!empty($order->admininfo)) {
                    $html .= "<br />&nbsp;<br />Additional info: " . nl2br(htmlspecialchars($order->admininfo));
                }

                $html .= $signTable;
            }
        } else {
            // INVOICE
            $html = '<h1 align="center"><img src="' . FCPATH . 'assets/images/bookabus.jpg" /></h1>				
					<table style="border-collapse: collapse;" valign="top" cellpadding="0" cellspacing="0" border="0" bordercolor="#6bca26" align="left" width="100%">	
						<tr>
							<td valign="top" style="width:100"><b>ATTN:</b></td>
							<td valign="top" style="width:400"></td>
							<td valign="top" style="width:160"><b>FROM:</b></td>
							<td valign="top" style="width:400"></td>
						</tr>
						<tr style="height:19px;">
							<td valign="top"><b>TO:</b></td>
							<td valign="top">' . $clientname . '</td>
							<td valign="top"><b>REP:</b></td>
							<td valign="top">Darren Lim<br/>
											Mobile: 94773707<br/>
											E-mail: darren@bookabus.sg</td>
						</tr>
						<tr style="height:19px;">
							<td valign="top"></td>
							<td valign="top">' . $uinfo->company . '<br/>' . $uinfo->address . '</td>
							<td valign="top"><b>ADDRESS:</b></td>
							<td valign="top">20 Bukit Batok Crescent<br/>
											#09-07 Enterprise Centre<br/>
											Singapore 658080</td>
						</tr>
						<tr>
							<td valign="top"><b>TEL:</b></td>
							<td valign="top">' . $uinfo->phone . '</td>
							<td valign="top"><b>TEL:</b></td>
							<td valign="top">+65 6600 1178</td>
						</tr>
						<tr>
							<td valign="top"><b>FAX:</b></td>
							<td valign="top"></td>
							<td valign="top"><b>FAX:</b></td>
							<td valign="top">+65 6569 6166</td>
						</tr>
						<tr>
							<td valign="top"><b>EMAIL:</b></td>
							<td valign="top">' . $uinfo->email . '</td>
							<td valign="top"><b>EMAIL:</b></td>
							<td valign="top"></td>
						</tr>
						<tr>
							<td valign="top"><b>For:</b></td>
							<td valign="top">Bus Services</td>
							<td valign="top"><b>Invoice No:</b></td>
							<td valign="top">' . $jobId . '</td>
						</tr>
						<tr>
							<td valign="top"><b>Date:</b></td>
							<td valign="top">' . date('jS F Y', strtotime($date)) . '</td>
							<td valign="top"><b>Validity :</b></td>
							<td valign="top">' . $validity . '</td>
						</tr>
						<tr>
							<td valign="top"></td>
							<td valign="top"></td>
							<td valign="top"><b>Terms:</b></td>
							<td valign="top">' . $terms . '</td>
						</tr>
						<tr>
							<td></td>
							<td></td>
							<td><b>Payment terms:</b></td>
							<td>' . $ptermText . '</td>
						</tr>
						<tr>
							<td></td>
							<td></td>
							<td><b>Payment methods:</b></td>
							<td>' . $strPmethod;
            if (strlen($fieldsHTML) > 0)
                $html .='<br/>' . $fieldsHTML;
            $html .='</td></tr>
						<tr>
						<td valign="top"></td>
						<td valign="top"></td>
						<td valign="top"><b>Payment due date:</b></td>
						<td valign="top">' . date('Y-m-d', strtotime($order->end_date . " +14 days")) . '</td>
					</tr>
					</table>
					<br/>
					<br/>
					<h1 align="center">' . (($invoice->proforma == 1) ? 'Quotation' : 'INVOICE') . '</h1>
				
				
				<br />

				<table  style="border: 1px solid #6bca26; border-collapse: collapse;" valign="top" cellpadding="10" cellspacing="0" border="0" bordercolor="#6bca26" align="left" width="100%">
					<tr bgcolor="#6bca26">
						<td valign="top" width="80px" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26;"><b>S/N</b></td>
						<td valign="top" width="540px" style="border-top: 1px solid #6bca26; "><b>Description</b></td>
						<td valign="top" width="80px" style="border-top: 1px solid #6bca26; "><b>Pax</b></td>
						<!--<td valign="top" style="border-top: 1px solid #6bca26; "><b>' . (($type == 1) ? "DATE (TIME)" : "TYPE") . '</b></td>-->
						<td valign="top" width="150px" style="border-top: 1px solid #6bca26; "><b>Bus Type</b></td>
						<td valign="top" width="110px" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26;"><b>' . (($type == 1) ? "Amount, per month (SGD)" : 'Amount (SGD)') . '</b></td>
					</tr>';
            if ($type == 1) {
                //recurring
                $routes = json_decode($order->routes);
                $c = 1;
                foreach ($routes as $route) {
                    for ($i = 0; $i < count($route->weekdays); $i++) {
                        $route->weekdays[$i] = substr($route->weekdays[$i], 0, 2);
                    }
                    $html .= '<tr style="border-top: 1px solid #6bca26; ">
								<td valign="top" style="border-top: 1px solid #6bca26; ">' . $c . '</td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">' . implode(', ', $route->weekdays) . '</td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">' . $route->bustype . '</td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
							</tr>';

                    // locations
                    foreach ($route->pickup as $pickup) {
                        $html .= '<tr style="border-top: 1px solid #6bca26; ">
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">Pickup Location: ' . $pickup->location . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $pickup->time . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								</tr>';
                    }
                    foreach ($route->dropoff as $dropoff) {
                        $html .= '<tr style="border-top: 1px solid #6bca26; ">
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">Drop Off Location: ' . $dropoff->location . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $dropoff->time . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								</tr>';
                    }

                    $html .= '<tr style="border-top: 1px solid #6bca26; ">
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">Total (Item ' . $c . ')</td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">' . number_format($route->price, 2, '.', ',') . '</td>
							</tr>';

                    $c++;
                }

                $html .= '<tr style="border-top: 1px solid #6bca26;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>Grand Total:</b></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>' . number_format($order->total_price, 2, '.', ',') . '</b></td>
						</tr>';
            } elseif ($type == 2) { // cancellation fee
                $html .= '<tr style="border-top: 1px solid #6bca26; ">
								<td valign="top" style="border-top: 1px solid #6bca26; ">1</td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">Cancelation fee</td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; "></td>
								<td valign="top" style="border-top: 1px solid #6bca26; ">' . number_format($order->cancelfee, 2, '.', ',') . '</td>
							</tr>';

                $html .= '<tr style="border-top: 1px solid #6bca26;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>Grand Total:</b></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>' . number_format($order->cancelfee, 2, '.', ',') . '</b></td>
						</tr>';
            } else {
                // jobs
                $c = 0;
                $j = $this->getOrderJobs($order->gid);
                foreach ($j as $job) {
                    //                            echo "<pre>";print_r($job);die;
                    $c++;
                    if ($job->job_type == 0 || $job->job_type == 1) {
                        $totalPriceRow = $job->total_price;
                        if ($totalPriceRow == 0) {
                            $totalPriceRow = "--";
                        }
                        $html .= '<tr style="border-top: 1px solid #6bca26; ">
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $c . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . nl2br($this->Jobs->formatJobInfo($job)) . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . (($job->job_type == 1) ? "Time Based" : "Trip Based") . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $job->bus_type . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $totalPriceRow . '</td>
								</tr>';
                    }
                }

                // Price changes
                //if ($invoice->proforma != 1) {
                $this->db->where('jid', $order->gid);
                $qc = $this->db->get('price_changes');
                $changes = $qc->result();
                foreach ($changes as $change) {
                    $c++;
                    $reason = ($change->amount{0} == "-") ? "Price decrease: " : "Price increase: ";
                    $reason .= $change->reason;
                    $html .= '<tr style="border-top: 1px solid #6bca26; ">
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $c . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . $reason . '</td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; "></td>
									<td valign="top" style="border-top: 1px solid #6bca26; ">' . number_format(str_replace(array('-', '+'), '', $change->amount), 2, '.', ',') . '</td>
								</tr>';
                }
                //}

                if ($type == 1) {
                    $html .= '<tr style="border-top: 1px solid #6bca26;  font-size: 45px;" bgcolor="#61c6d8">
						<td valign="top" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
						<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
						<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
						<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>Grand Total:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>' . number_format($order->price + ($order->price * $order->tax / 100) - ($order->price * $order->discount / 100), 2, '.', ',') . '</b></td>
					</tr>';
                } else {
                    $total = $order->price;
                    $grandtotal = $order->price + ($order->price * $order->tax / 100) - ($order->price * $order->discount / 100);
                    $paid = $order->paid;
                    $html .= '<tr style="border-top: 1px solid #6bca26; ">
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>Total:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>' . number_format($total, 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #6bca26; ">
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>Tax:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>' . number_format(($order->price * $order->tax / 100), 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #6bca26; ">
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>Discount:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>-' . number_format(($order->price * $order->discount / 100), 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #6bca26;  font-size: 45px;" bgcolor="#6bca26">
						<td valign="top" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26;"></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>Grand Total:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26;"><b>' . number_format($grandtotal, 2, '.', ',') . '</b></td>
					</tr>';

                    if ($invoice->partial == 1) {
                        $html .= '<tr style="border-top: 1px solid #6bca26; ">
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>Required in advance:</b></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>' . number_format($order->preq, 2, '.', ',') . '</b></td>
						</tr>';
                    } else {
                        $html .= '<tr style="border-top: 1px solid #6bca26; ">
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>Already paid:</b></td>
						<td valign="top" style="border-top: 1px solid #6bca26; "><b>-' . number_format($paid, 2, '.', ',') . '</b></td>
					</tr>
					<tr style="border-top: 1px solid #6bca26;  font-size: 45px;" bgcolor="#61c6d8">
							<td valign="top" style="border-top: 1px solid #6bca26; border-left: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>Left to pay:</b></td>
							<td valign="top" style="border-top: 1px solid #6bca26; border-right: 1px solid #6bca26; border-bottom: 1px solid #6bca26;"><b>' . number_format($grandtotal - $paid, 2, '.', ',') . '</b></td>
						</tr>';
                    }
                }
            }
            $html .= '</table>';
            $signTable = '';
            /*
              $signTable = '<br /><br />
              <table cellspacing="0" cellpadding="0" border="0" width="100%" style="padding-top:10px;">
              <tr>
              <td align="left" style="width:68%;">

              </td>
              <td align="left" style="width:31%;">
              Agreed & Accepted By:<br/><br/><br/><br /><br />
              ____________________________<br/>
              Signature/Name/Company Stamp<br/>
              Date: ___________________<br />
              Please sign/stamp and fax to: +65 6569 6166
              </td>
              </tr>
              </table>';
             */
            if ($type == 1) {
                $html .= '
	<p>Note:</p>
	<ol>
		<li>Quotation valid for 2 weeks subject to availability of Buses</li>
		<li>No GST Chargeable</li>
		<li>1 week notice required before commencement of Services to facilitate Driver Orientation</li>
	</ol>
	<h2 align="center">THANK YOU FOR YOUR CONSIDERATION</h2>';

                $html .= $signTable;
            } else {
                if (!empty($order->admininfo)) {
                    //$html .= "<br />&nbsp;<br />Additional info: ".nl2br(htmlspecialchars($order->admininfo));
                }

                $html .= "<br />&nbsp;<br />";
                $html .= "Cheque payment should be crossed and made payable to 'BOOKABUS PTE LTD'<br/><br/>";
                $html .= "<table  style='border: 1px solid #68c723; border-collapse: collapse;' valign='top' cellpadding='10' cellspacing='0' border='0' bordercolor='#CCCCCC' align='left' width='400'>
							<tr>
								<td width='300'>Bank details are as follows:</td>
								<td>BOOKABUS PTE LTD</td>
								<td></td>
							</tr>
							<tr>
								<td></td>
								<td style='text-align: left;'>OCBC Bank A/C No: 588 03292 0001</td>
								<td></td>
							</tr>
						</table>";
                $html .= "This is a computer-generated document, no signature is required.";
                $html .= $signTable;
            }
        }
        $pdf->writeHTML($html, true, false, true, false, '');
        $pdf->lastPage();

        $pdf->Output('quotation-' . $jobId . 'pdf', 'I');
    }
}
?>